# -*- coding:utf-8 -*-


import os
curDir = os.getcwd()
print curDir    #/home/ding/PycharmProjects/SQLAlchemyTest/test

file = curDir + '/foo.db'


from sqlalchemy import create_engine, MetaData,\
        Table, Column, Integer, String, ForeignKey

engine = create_engine('sqlite:///' + file, echo = False)  # create_engine('sqlite:///D:/mycode/SQLAlchemyTest/test/foo.db', echo = False)


conn = engine.connect()

#https://segmentfault.com/q/1010000004430528/a-1020000004464738
class SrcData():pass
src_data1 = SrcData()

#https://stackoverflow.com/questions/10588375/can-i-assign-values-in-rowproxy-using-the-sqlalchemy
class DBRow(dict):
    def __getattr__(self, key):
        """make values available as attributes"""
        try:
            return self[key]
        except KeyError as error:
            raise AttributeError(str(error))

resultProxy=engine.execute('select * from user where id = 1')
src_data1.data = DBRow(resultProxy.fetchone())
print src_data1.data

resultProxy=engine.execute('select * from address where user_id = :id', src_data1.data.id)   #或src_data1.data['id']
src_data1.data['address'] = [DBRow(row) for row in resultProxy.fetchall()]  #用src_data1.data.address = 并不能添加key
print src_data1.data
#print src_data1.data.address
#print src_data1.data['address']


src_data2 = SrcData()
src_data2.data = DBRow({'test': 2})
print src_data2.data
print src_data2.data.test   #不用DBRow这里会报错


metadata = MetaData(engine)
rule_data_user = Table('rule_data_user', metadata,
        Column('id', Integer),
        Column('name', String(150)),
        Column('emails', String(500)),
        Column('test', Integer)
        )

rule_data_user.create(checkfirst=True)


 
target_table = Table("rule_data_user", metadata, autoload = True)    #既然是动态语言，提前生成model也没有多少意义



def strjoin(strs):
    return lambda: ", ".join(strs)

def throwNotImplException():
    raise Exception, "没有实现"
 
def fill_data(target_table, dc, dw):

    switcher = {
        "id": lambda: dc.data.id,
        "name":lambda: dc.data.fullname + " (" + dc.data.name + ")",
        "emails": strjoin([i.email for i in dc.data.address]),
        #"test": lambda: dw.data.test % 2
    }
    
    values = {}
    for c in target_table.columns:
        print c.name, ":",

        # Get the function from switcher dictionary
        func = switcher.get(c.name, throwNotImplException)

        values[c.name] = func()
        print values[c.name]

    return values



result = fill_data(target_table, src_data1, src_data2)
print result

conn.execute(target_table.insert(), [result])


 

